package assignments.sagarjoshi.springtms;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by SCTDC\pranav.ghaisas on 20/1/15.
 */
public class MyDatabaseHelper extends SQLiteOpenHelper {

    //database version
    public static final int DATABASE_VERSION = 1;
    //database name
    public static final String DATABASE_NAME = "TraineeDatabase.db";
    //table names
    public static final String DATABASE_TABLE_TRAINEE = "Trainee";
    public static final String DATABASE_TABLE_COURSE = "Course";
    public static final String DATABASE_TABLE_LOGIN = "Login";
    public static final String DATABASE_TABLE_TRAINEE_COURSE = "Trainee_Course";
    //trainee table columns
    public static final String KEY_TRAINEE_IDS = "trainee_id";
    public static final String KEY_TRAINEE_NAME = "trainee_name";
    public static final String KEY_TRAINEE_COLLEGE_NAME = "college_name";
    public static final String KEY_TRAINEE_TECHNOLOGY = "technology";
    //private static final String KEY_TRAINEE_DOB = "dob";
    //private static final String KEY_TRAINEE_ADDRESS = "address";
    public static final String KEY_TRAINEE_JOINING_DATE = "joining_date";
    //private static final String KEY_TRAINEE_BLOOD_GROUP = "blood_group";
    //course table columns
    public static final String KEY_COURSE_NAME = "course_name";
    public static final String KEY_COURSE_DESCRIPTION = "course_description";
    public static final String KEY_COURSE_MENTOR_NAME = "mentor_name";
    public static final String KEY_COURSE_PLACE = "place";
    //Login table columns
    public static final String KEY_LOGIN_ID = "_id";
    public static final String KEY_EMAIL_ID = "email_id";
    public static final String KEY_PASSWORD = "password";
    public static final String KEY_ROLE = "role";
    //Trainee_Course (Many to Many) table columns
    //private static final String KEY_TRAINEE_COURSE_ID = "_trainee_course_id";
    public static final String KEY_ALLOCATED_DATE = "allocated_date";
    public static final String KEY_TRAINEE_ID = "trainee_id";
    public static final String KEY_COURSE_ID = "course_id";
    public static final String KEY_STATUS = "status";
    public static final String KEY_COMMENTS = "comments";
    public static final String KEY_ID = "_id";


    public MyDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    /**
     * Called when the database is created for the first time. This is where the
     * creation of tables and the initial population of the tables should happen.
     *KEY_COURSE_ID
     * @param db The database.
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTraineeTable = "create table "+ DATABASE_TABLE_TRAINEE + "(" +
                                KEY_ID + " integer primary key AUTOINCREMENT," +
                                KEY_TRAINEE_IDS + " text," +
                                KEY_TRAINEE_NAME + " text," +
                                KEY_EMAIL_ID +  " text UNIQUE," +
                                KEY_TRAINEE_COLLEGE_NAME + " text," +
                                KEY_TRAINEE_TECHNOLOGY + " text," +
                                KEY_TRAINEE_JOINING_DATE + " text"+ ")";
        db.execSQL(createTraineeTable);

        String createCourseTable = "create table "+ DATABASE_TABLE_COURSE + "(" +
                                    KEY_ID + " integer primary key AUTOINCREMENT," +
                                    KEY_COURSE_NAME + " text,"+
                                    KEY_COURSE_DESCRIPTION + " text," +
                                    KEY_COURSE_PLACE + " text," +
                                    KEY_COURSE_MENTOR_NAME + " text" + ")";
        db.execSQL(createCourseTable);

        String createLoginTable = "create table "+ DATABASE_TABLE_LOGIN + "(" +
                                    KEY_ID + " integer primary key AUTOINCREMENT," +
                                    KEY_EMAIL_ID + " text," +
                                    KEY_PASSWORD + " text," +
                                    KEY_ROLE + " text" + ")";
        db.execSQL(createLoginTable);

        String createTraineeCourseTable = "create table "+ DATABASE_TABLE_TRAINEE_COURSE + "("+
                                            KEY_ID + " integer primary key AUTOINCREMENT," +
                                            KEY_ALLOCATED_DATE + " text," +
                                            KEY_STATUS + " text," +
                                            KEY_COMMENTS + " text," +
                                            KEY_TRAINEE_ID + " integer," +KEY_COURSE_ID + " integer," +
                                            "FOREIGN KEY("+KEY_TRAINEE_ID+") REFERENCES "+ DATABASE_TABLE_TRAINEE+"("+KEY_ID+"),"+

                                            "FOREIGN KEY("+KEY_COURSE_ID+") REFERENCES "+ DATABASE_TABLE_COURSE+"("+KEY_ID+"))";
        db.execSQL(createTraineeCourseTable);

        ContentValues contentValues = new ContentValues();
        Login login = new Login("admin@springcomputing.in","admin123","admin");
        contentValues.put(KEY_EMAIL_ID,login.getEmail());
        contentValues.put(KEY_PASSWORD,login.getPassword());
        contentValues.put(KEY_ROLE, login.getRole_name());

        db.insert(DATABASE_TABLE_LOGIN,null,contentValues);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS" + DATABASE_TABLE_TRAINEE);
        db.execSQL("DROP TABLE IF EXISTS" + DATABASE_TABLE_COURSE);
        db.execSQL("DROP TABLE IF EXISTS" + DATABASE_TABLE_LOGIN);
        db.execSQL("DROP TABLE IF EXISTS" + DATABASE_TABLE_TRAINEE_COURSE);

        onCreate(db);
    }

    /**
     *It adds records in Trainee table
     * */
    public int AddTrainee(Context context,Trainee trainee){
        SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_TRAINEE_IDS, trainee.getTrainee_id());
        contentValues.put(KEY_TRAINEE_NAME, trainee.getTrainee_name());
        contentValues.put(KEY_TRAINEE_COLLEGE_NAME, trainee.getCollege_name());
        contentValues.put(KEY_TRAINEE_TECHNOLOGY, trainee.getTechnology());
        contentValues.put(KEY_TRAINEE_JOINING_DATE, trainee.getJoining_date());
        contentValues.put(KEY_EMAIL_ID,trainee.getEmail());

        String trainee_id = trainee.getTrainee_id();
        //String[] projection = {MyDatabaseHelper.KEY_TRAINEE_IDS};
        String query = "select * from Trainee where trainee_id=?";
        Cursor cursor = sqLiteDatabase.rawQuery(query,new String[]{trainee_id});
        if(cursor.getCount()>0) {
            Toast.makeText(context, "Trainee ID already exists...", Toast.LENGTH_LONG).show();
            return 1;
        }
        else {
            long status = sqLiteDatabase.insert(DATABASE_TABLE_TRAINEE, null, contentValues);
            if (status == 0) {
                Toast.makeText(context, "Records not added...", Toast.LENGTH_LONG).show();
                sqLiteDatabase.close();
                return 1;
            } else {
                String email = trainee.getEmail();
                String[] arr = trainee.getTrainee_name().split(" ");
                String password = arr[0] + "123";
                String role_name="trainee";
                ContentValues cv = new ContentValues();
                cv.put(KEY_EMAIL_ID, email);
                cv.put(KEY_PASSWORD, password);
                cv.put(KEY_ROLE,role_name);

                long res = sqLiteDatabase.insert(DATABASE_TABLE_LOGIN, null, cv);
                Toast.makeText(context, "Record Added successfully...!!!", Toast.LENGTH_LONG).show();
                sqLiteDatabase.close();
                    return 0;
             }
        }

    }

    public Cursor EditTrainee(int trainee_id){
        SQLiteDatabase sqLiteDatabase = this.getReadableDatabase();
        String editQuery="select * from "+DATABASE_TABLE_TRAINEE+" where _id=?";
        Cursor cursor = sqLiteDatabase.rawQuery(editQuery,new String[]{String.valueOf(trainee_id)});
        cursor.moveToFirst();
        return cursor;
    }

    public int UpdateTrainee(Trainee cur_trainee){
        SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
        String updateSQL="update "+DATABASE_TABLE_TRAINEE+" set "+
                KEY_TRAINEE_IDS+"=?,"+
                KEY_TRAINEE_NAME+"=?"+
                KEY_EMAIL_ID+"=?"+
                KEY_TRAINEE_COLLEGE_NAME+"=?"+
                KEY_TRAINEE_TECHNOLOGY+"=?"+
                KEY_TRAINEE_JOINING_DATE+"=?";
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_TRAINEE_IDS,cur_trainee.getTrainee_id());
        contentValues.put(KEY_TRAINEE_NAME, cur_trainee.getTrainee_name());
        contentValues.put(KEY_EMAIL_ID,cur_trainee.getEmail());
        contentValues.put(KEY_TRAINEE_COLLEGE_NAME,cur_trainee.getCollege_name());
        contentValues.put(KEY_TRAINEE_TECHNOLOGY, cur_trainee.getTechnology());
        contentValues.put(KEY_TRAINEE_JOINING_DATE, cur_trainee.getJoining_date());

        int res=sqLiteDatabase.update(DATABASE_TABLE_TRAINEE,contentValues,"_id=?",new String[]{String.valueOf(cur_trainee.getId())});
        return res;
    }

    /**
     * Getting the list of all trainees such as
     * in sql>> select * from Trainee
     */
    public List<Trainee> getAllTrainees( ){
        List<Trainee> trainees = new ArrayList<Trainee>();
        String selectQuery = "select * from " + DATABASE_TABLE_TRAINEE;
        Log.i("TraineeSelectQueryResult",selectQuery);

        SQLiteDatabase sqLiteDatabase = this.getReadableDatabase();
        Cursor cursor = sqLiteDatabase.rawQuery(selectQuery, null);



        if (cursor.moveToFirst()){
            do {
                Trainee trainee = new Trainee();
                trainee.setTrainee_id(cursor.getString(cursor.getColumnIndex(KEY_TRAINEE_IDS)));
                trainee.setTrainee_name(cursor.getString(cursor.getColumnIndex(KEY_TRAINEE_NAME)));
                trainee.setCollege_name(cursor.getString(cursor.getColumnIndex(KEY_TRAINEE_COLLEGE_NAME)));
                trainee.setTechnology(cursor.getString(cursor.getColumnIndex(KEY_TRAINEE_TECHNOLOGY)));
                trainee.setJoining_date(cursor.getString(cursor.getColumnIndex(KEY_TRAINEE_JOINING_DATE)));

                trainees.add(trainee);
            }while (cursor.moveToNext());
        }
        return trainees;
    }

    /**
     * It adds records in Course Table
     */
    public long AddCourse(Course course){
        SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_COURSE_NAME, course.getCourse_name());
        contentValues.put(KEY_COURSE_DESCRIPTION, course.getCourse_description());
        contentValues.put(KEY_COURSE_MENTOR_NAME, course.getMentor_name());
        contentValues.put(KEY_COURSE_PLACE,course.getPlace());

        long status = sqLiteDatabase.insert(DATABASE_TABLE_COURSE, null, contentValues);
        sqLiteDatabase.close();
        return status;
    }

    public Cursor EditCourse(int course_id){
        SQLiteDatabase sqLiteDatabase=this.getReadableDatabase();
        String selectQuery="select * from "+DATABASE_TABLE_COURSE+" where _id=?";
        Cursor cursor = sqLiteDatabase.rawQuery(selectQuery,new String[]{String.valueOf(course_id)});
        cursor.moveToFirst();
        Log.i("Successful status of cursor", String.valueOf(cursor.getCount()));
        return cursor;
    }

    public int UpdateCourse(Course cur_course){
        SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
        String updateQuery="update "+DATABASE_TABLE_COURSE+" set "+
                            KEY_COURSE_NAME+"=?,"+
                            KEY_COURSE_DESCRIPTION+"=?"+
                            KEY_COURSE_PLACE+"=?"+
                            KEY_COURSE_MENTOR_NAME+"=?";
                           // " where _id=?";
        //Course course = new Course(course_id);
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_COURSE_NAME, cur_course.getCourse_name());
        contentValues.put(KEY_COURSE_DESCRIPTION, cur_course.getCourse_description());
        contentValues.put(KEY_COURSE_MENTOR_NAME, cur_course.getMentor_name());
        contentValues.put(KEY_COURSE_PLACE,cur_course.getPlace());
        //contentValues.put(KEY_ID,course_id);

        Log.i("Course Name:",cur_course.getCourse_name());

        int res=sqLiteDatabase.update(DATABASE_TABLE_COURSE,contentValues,"_id=?",new String[]{String.valueOf(cur_course.getId())});
        return res;
    }
    /**
     * Getting the list of all Courses such as
     * in sql>> select * from Course
     */
    public List<Course> getAllCourses() {
        List<Course> courses = new ArrayList<Course>();
        String selectQuery = "select * from "+ DATABASE_TABLE_COURSE;
        Log.i("CourseSelectQueryResult",selectQuery);

        SQLiteDatabase sqLiteDatabase = this.getReadableDatabase();
        Cursor cursor = sqLiteDatabase.rawQuery(selectQuery,null);

        if (cursor.moveToFirst()){
            do {
                Course course = new Course();
                course.setCourse_name(cursor.getString(cursor.getColumnIndex(KEY_COURSE_NAME)));
                course.setCourse_description(cursor.getString(cursor.getColumnIndex(KEY_COURSE_DESCRIPTION)));
                course.setMentor_name(cursor.getString(cursor.getColumnIndex(KEY_COURSE_MENTOR_NAME)));

                courses.add(course);
            }while (cursor.moveToNext());
        }
        return courses;
    }
    /**
     * It checks the Login
     */
    public Cursor CheckLogin(Context context,Login login, String username, String password){
        SQLiteDatabase sqLiteDatabase = this.getReadableDatabase();
        String[] projection =   {
                                    MyDatabaseHelper.KEY_EMAIL_ID,
                                    MyDatabaseHelper.KEY_PASSWORD,
                                    MyDatabaseHelper.KEY_ROLE
                                };
        String selectionQuery=MyDatabaseHelper.KEY_EMAIL_ID+"=? and "+MyDatabaseHelper.KEY_PASSWORD+"=?";
        String[] selectionargs=new String[]{username,password};
        Cursor cursor = sqLiteDatabase.query(
                                                DATABASE_TABLE_LOGIN,
                                                projection,
                                                selectionQuery,
                                                selectionargs,
                                                null,
                                                null,
                                                null
                                            );

        /*cursor.moveToFirst();
        String qUsername = cursor.getString(cursor.getColumnIndexOrThrow(MyDatabaseHelper.KEY_EMAIL_ID));
        String qPassword = cursor.getString(cursor.getColumnIndexOrThrow(MyDatabaseHelper.KEY_PASSWORD));
        String qRole = cursor.getString(cursor.getColumnIndexOrThrow(MyDatabaseHelper.KEY_ROLE));
        /*if(username.equals(qUsername) && password.equals(qPassword)){
            return 1;
        }
        else{
            return 0;
        }*/
        return cursor;
    }
 }
